import pandas as pd

# 读取CSV文件
df = pd.read_csv('summerOly_athletes.csv')

# 选择需要的列
df = df[['NOC', 'Year', 'Sport', 'Event', 'Medal']]

# 创建金银铜牌统计
medals_df = df[df['Medal'] != 'No medal'].groupby(['Year', 'NOC', 'Medal']).size().unstack(fill_value=0)

# 如果没有某种奖牌，确保列存在
for medal_type in ['Gold', 'Silver', 'Bronze']:
    if medal_type not in medals_df.columns:
        medals_df[medal_type] = 0

# 计算总奖牌数
medals_df['Total'] = medals_df['Gold'] + medals_df['Silver'] + medals_df['Bronze']

# 按年份和总奖牌数排序
medals_df = medals_df.reset_index()
medals_df = medals_df.sort_values(['Year', 'Gold', 'Silver', 'Bronze'], 
                                ascending=[True, False, False, False])

# 在每个年份内添加排名
medals_df['Rank'] = medals_df.groupby('Year')['Gold'].rank(method='min', ascending=False)

# 重新排列列顺序
medals_df = medals_df[['Year', 'NOC', 'Rank', 'Gold', 'Silver', 'Bronze', 'Total']]

# 保存结果
medals_df.to_csv('olympic_medals_by_country.csv', index=False)
